from src.common.ds import AIDES_DB
from src.repo.model.deployment import *


# 部署相关
def query_deploy_by_project_env(project_id, environment_id) -> Deployment:
    sql = f"select * from deployment where project_id = {project_id} and environment_id = {environment_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Deployment(**rs) if rs else None


def create_deployment(deployment: Deployment):
    sql = (f"insert into deployment(project_id, environment_id) values "
           f"({deployment.project_id}, {deployment.environment_id})")
    _id = AIDES_DB.execute_insert_with_id(sql)
    deployment.id_ = _id
    return deployment


def create_deployment_merge_branch(model: DeploymentMergeBranch):
    sql = (f"insert into deployment_merge_branch(deployment_id, branch_name, status) values "
           f"({model.deployment_id}, '{model.branch_name}', '{model.status}')")
    _id = AIDES_DB.execute_insert_with_id(sql)
    model.id_ = _id
    return model


def query_merge_branch_by_deployment_id(deployment_id) -> DeploymentMergeBranch:
    sql = f"select * from deployment_merge_branch where deployment_id = {deployment_id} and status = 0"
    rs = AIDES_DB.execute_query_one(sql)
    return DeploymentMergeBranch(**rs) if rs else None


def query_deployment_by_env(environment_id) -> Deployment:
    sql = f"select * from deployment where environment_id = {environment_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Deployment(**rs) if rs else None


def query_deploy_branches(deployment_id) -> [DeploymentBranch]:
    sql = (f"select db.*, p.local_folder, "
           f"b.branch_name, f.id_ as feature_id, f.name as feature_name "
           f"from deployment_branch db "
           f"left join branch b on db.branch_id = b.id_ "
           f"left join feature f on b.feature_id = f.id_ "
           f"left join project p on b.project_id = p.id_ "
           f"where db.deployment_id = {deployment_id}")
    return [DeploymentBranch(**r) for r in AIDES_DB.execute_query(sql)]


def create_deploy_branch(deployment_id, branch_id, latest_commit):
    sql = (f"insert into deployment_branch(deployment_id, branch_id, latest_commit) values "
           f"({deployment_id}, {branch_id}, '{latest_commit}')")
    AIDES_DB.execute_insert(sql)


def update_merge_branch_status(deployment_id, merge_status):
    sql = (f"update deployment_merge_branch set merge_status = '{merge_status}' "
           f"where deployment_id = {deployment_id} and status = '{DeleteStatus.NOT_DELETED}'")
    AIDES_DB.execute_update(sql)


def update_deploy_branch_latest_commit(deployment_id, branch_id, latest_commit, merge_status):
    sql = (f"update deployment_branch set latest_commit = '{latest_commit}', merge_status = '{merge_status}' "
           f"where deployment_id = {deployment_id} and branch_id = {branch_id}")
    AIDES_DB.execute_update(sql)


# 项目相关
def query_project_by_id(project_id) -> Project:
    sql = f"select * from project where id_ = {project_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Project(**rs) if rs else None


def query_projects() -> [Project]:
    return [Project(**r) for r in AIDES_DB.execute_query("select * from project")]


# 环境相关
def query_env_by_id(project_id) -> Environment:
    sql = f"select * from environment where id_ = {project_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Environment(**rs) if rs else None


# 分支相关
def query_branch_by_id(branch_id) -> Branch:
    sql = f"select * from branch where id_ = {branch_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Branch(**rs) if rs else None


def query_branch_project_and_feature(project_id, feature_id) -> Branch:
    sql = f"select * from branch where project_id = {project_id} and feature_id = {feature_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Branch(**rs) if rs else None


def query_branches(project_id, status: BranchStatus) -> [Branch]:
    sql = (f"select b.*, f.name feature_name from branch b left join feature f on b.feature_id = f.id_ "
           f"where b.project_id = {project_id} and b.status = '{status}'")
    rs = AIDES_DB.execute_query(sql)
    return [Branch(**r) for r in rs]


def create_branch(branch: Branch):
    sql = (f"insert into branch(project_id, feature_id, branch_name, status) values "
           f"({branch.project_id}, {branch.feature_id}, '{branch.branch_name}', '{branch.status}')")
    _id = AIDES_DB.execute_insert_with_id(sql)
    branch.id_ = _id
    return branch


# 需求相关
def query_feature_by_id(feature_id) -> Feature:
    sql = f"select * from feature where id_ = {feature_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return Feature(**rs) if rs else None


# 执行流程相关
def create_deployment_process(dp: DeploymentProcess):
    sql = (f"insert into deployment_process(environment_id,  deployment_id, status, process_log) values "
           f"({dp.environment_id}, {dp.deployment_id}, '{dp.status}', '{dp.process_log}')")
    _id = AIDES_DB.execute_insert_with_id(sql)
    dp.id_ = _id
    return dp


def query_deployment_process_by_id(dp_id) -> DeploymentProcess:
    sql = f"select * from deployment_process where id_ = {dp_id}"
    rs = AIDES_DB.execute_query_one(sql)
    return DeploymentProcess(**rs) if rs else None


def update_deployment_process(dp_id, status: DeploymentProcessStatus, finished_time):
    sql = (f"update deployment_process set status = '{status}', finished_time = '{finished_time}' "
           f"where id_ = {dp_id}")
    AIDES_DB.execute_update(sql)


def query_active_deployment_process(environment_id) -> DeploymentProcess:
    sql = (f"select * from deployment_process where environment_id = {environment_id} "
           f"and status != '{DeploymentProcessStatus.FINISHED}'")
    rs = AIDES_DB.execute_query_one(sql)
    return DeploymentProcess(**rs) if rs else None
